﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Sql
{
    class SQL
    {
        //public static string sqlStr;
        //public static SqlConnection sqlConnection = new SqlConnection();
        public static string connectionString = "Server=.;Database=design;User ID=sa;Password=root";
        //创建连接对象
        public static SqlConnection conn = new SqlConnection();
        /// <summary>
        /// 打开数据库连接，成功返回true，失败返回false
        /// </summary>
        /// <returns></returns>
        public static bool Open()
        {
            //连接属性赋值
            conn.ConnectionString = connectionString;
            //尝试打开连接
            try
            {
                conn.Open();
                if (conn.State == ConnectionState.Open)
                {

                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch
            {
                return false;
            }
        }
        public static bool Close()
        {
            if (conn.State == ConnectionState.Open)
            {
                try
                {
                    conn.Close();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
            else
            {
                return true;
            }
        }
        public static bool Querys(string sqlStr, string colum)
        {

            Open();
            //返回查询结果
            try
            {
                SqlCommand command = new SqlCommand(sqlStr, conn);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {                // 可以使用数据库中的字段名，也可以使用角标访问                
                    if (reader[colum].ToString() != null)
                    {
                        reader.Close();
                        return true;
                    }

                }
            }
            catch
            {
                MessageBox.Show("异常！！！");
                return false;

            }
            finally
            {
                Close();

            }
            return false;


        }
        public static bool SqlInsert(string sqlStr)
        {
            Open();
            //返回查询结果
            //sqlStr = "insert into Borrow values('182056204','数据库','2020-05-02','15',null)";
            try
            {
                SqlCommand command = new SqlCommand(sqlStr, conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                MessageBox.Show("异常！！！");
                return false;

            }
            finally
            {
                Close();

            }
        }
        public static bool SqlUpdate(string sqlStr)
        {
            Open();
            //返回查询结果
            //sqlStr = "update Book set Num_remain=Num_remain-1 where B_name='"+ textBox1.Text+"'";
            try
            {
                SqlCommand command = new SqlCommand(sqlStr, conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                MessageBox.Show("异常！！！");
                return false;

            }
            finally
            {
                Close();

            }
            
        }
        public static bool SqlDelete(string sqlStr)
        {
            Open();
            //返回查询结果
            //sqlStr = "update Book set Num_remain=Num_remain-1 where B_name='"+ textBox1.Text+"'";
            try
            {
                SqlCommand command = new SqlCommand(sqlStr, conn);
                command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                MessageBox.Show("异常！！！");
                return false;

            }
            finally
            {
                Close();

            }

        }

    }
}
